package com.isec.base.monit.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import cn.hutool.cache.CacheUtil;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.core.security.page.AutoPage;
import com.core.tools.AppUserTool;
import com.core.tools.PropertiesTool;
import com.core.util.Sha256;
import com.isec.base.monit.dao.DataLogDao;
import com.isec.base.monit.dao.DownPmDao;
import com.isec.base.monit.dao.PmDao;
import com.isec.base.monit.dto.DataLogDto;
import com.isec.base.monit.dto.DownPmDto;
import com.isec.base.monit.dto.PmDto;

import cn.hutool.core.lang.UUID;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;

@Service
public class PmService {

	@Autowired
	PmDao pmDao;

	@Autowired
	DataLogDao dataLogDao;
	
	@Autowired
	DownPmDao downPmDao;

	public PmDao getPmDao() {
		return pmDao;
	}

	public JSONArray loadData(String ct) {
		List<PmDto> pms = new ArrayList<PmDto>();
		if (StringUtils.equals(ct, "all") || StringUtils.isEmpty(ct)) {
			pms = pmDao.getList("select * from idgar_pm_tab");
		}else {
			pms = pmDao.getList("select * from idgar_pm_tab where EleType = ?", ct);
		}
		if (null == pms) {
			return new JSONArray();
		}
		return JSONUtil.parseArray(pms);
	}
	
	public List<PmDto> loadPte() {
		
		return pmDao.getList("SELECT EleType Content FROM IDGAR_PM_TAB GROUP BY EleType");
	}

	public AutoPage<DownPmDto> searchData(AutoPage<DownPmDto> page,String ct) {
		String sql = "SELECT T.IsotopeType,cast(T.No as SIGNED) No,t.pmType,T.Content,T.Nation,SUM(t.dataDowncount) dataDowncount FROM (select a.*,CAST(a.No AS SIGNED) No1,b.dataDowncount from idgar_pm_tab a,igdar_datalog_tab b where a.pm_id = b.dataId and b.dataType = '1' and a.EleType = '"+ct+"' ORDER BY a.`No`+1) T GROUP BY t.No,T.IsotopeType,T.Content,t.PMType ORDER BY t.No1";
		return downPmDao.findAutoPageBySQL(page, sql);
	}

	public JSONArray loadElements() {
		String sql = "SELECT A.EleType FROM (select DISTINCT EleType from idgar_pm_tab) A,idgar.IDGAR_ELEMENTS_TAB B WHERE A.EleType = B.ELE_NAME ORDER BY B.ELE_INDEX";
		List<Map<String,Object>> ls = pmDao.getMapList(sql);
		return JSONUtil.parseArray(ls);
	}

	public List<String[]> downData(String params) {
		if (StringUtils.isEmpty(params)) {
			return null;
		}
		JSONArray arr = JSONUtil.parseArray(params);
		String where = " AND (";
		for (int i = 0; i < arr.size(); i++) {
			if (i != arr.size()-1) {
				where += "(IsotopeType = '" + arr.getJSONObject(i).getStr("IsotopeType") + "' and PMType = '" + arr.getJSONObject(i).getStr("Type") + "' and Content = '" + arr.getJSONObject(i).getStr("Content") + "' and No = '" + arr.getJSONObject(i).getStr("No") + ".0' AND Nation = '" + arr.getJSONObject(i).getStr("Nation") + "') OR ";
			}else {
				where += "(IsotopeType = '" + arr.getJSONObject(i).getStr("IsotopeType") + "' and PMType = '" + arr.getJSONObject(i).getStr("Type") + "' and Content = '" + arr.getJSONObject(i).getStr("Content") + "' and No = '" + arr.getJSONObject(i).getStr("No") + ".0' AND Nation = '" + arr.getJSONObject(i).getStr("Nation") + "')";
			}
		}
		where += ")";
		if (arr.size() == 0) {
			where = "";
		}
		List<PmDto> pms = pmDao.getList("select * from idgar_pm_tab WHERE 1 = 1" + where); 
		String[] row = null;
		PmDto pmDto = null;
		List<String[]> excels = new ArrayList<String[]>();
		for (int i = 0; i < pms.size(); i++) {
			pmDto = pms.get(i);
			row = new String[19];
			row[0] = pmDto.getNo();
			row[1] = pmDto.getIsotopeType();//
			row[2] = pmDto.getPMType();//
			row[3] = pmDto.getContent();//
			row[4] = pmDto.getIc();//
			row[5] = pmDto.getIcsd();//
			row[6] = pmDto.getN();//
			row[7] = pmDto.getMc();//
			row[8] = pmDto.getMcsd();//
			row[9] = pmDto.getSamplingDate();//
			row[10] = pmDto.getYear();//
			row[11] = pmDto.getSeason();//
			row[12] = pmDto.getSamplingSite();//
			row[13] = pmDto.getNation();//
			row[14] = pmDto.getRegion();//
			row[15] = pmDto.getLatitude();//
			row[16] = pmDto.getLongitude();//
			row[17] = pmDto.getLaboratory();//
			row[18] = pmDto.getReference();
			excels.add(row);
		}
		pmDao.excute("UPDATE IGDAR_DATALOG_TAB SET DATADOWNCOUNT = DATADOWNCOUNT + 1 WHERE DATAID IN(SELECT PM_ID FROM IDGAR_PM_TAB WHERE 1 = 1 " + where + ")"); 
		return excels;
	}

	public List<PmDto> analyzePms(List<Map<Integer, Object>> rows) {
		if (rows == null || rows.size() == 0) {
			return null;
		}
		List<PmDto> pms = new ArrayList<PmDto>();
		PmDto pmDto = null;
		for (int i = 0; i < rows.size(); i++) {
			if (rows.get(i) == null || null == rows.get(i).get(0)){
				continue;
			}
			pmDto = new PmDto();
			pmDto.setEleType(rows.get(i).get(999).toString());
			pmDto.setNo(null == rows.get(i).get(0)?"":rows.get(i).get(0).toString());
			pmDto.setIsotopeType(null == rows.get(i).get(1)?"":rows.get(i).get(1).toString());
			pmDto.setPMType(null == rows.get(i).get(2)?"":rows.get(i).get(2).toString());
			pmDto.setContent(null == rows.get(i).get(3)?"":rows.get(i).get(3).toString());
			pmDto.setIc(null == rows.get(i).get(4)?"":rows.get(i).get(4).toString());
			pmDto.setIcsd(null == rows.get(i).get(5)?"":rows.get(i).get(5).toString());
			pmDto.setN(null == rows.get(i).get(6)?"":rows.get(i).get(6).toString());
			pmDto.setMc(null == rows.get(i).get(7)?"":rows.get(i).get(7).toString());
			pmDto.setMcsd(null == rows.get(i).get(8)?"":rows.get(i).get(8).toString());
			pmDto.setSamplingDate(null == rows.get(i).get(9)?"":rows.get(i).get(9).toString());
			pmDto.setYear(null == rows.get(i).get(10)?"":rows.get(i).get(10).toString());
			pmDto.setSeason(null == rows.get(i).get(11)?"":rows.get(i).get(11).toString());
			pmDto.setSamplingSite(null == rows.get(i).get(12)?"":rows.get(i).get(12).toString());
			pmDto.setNation(null == rows.get(i).get(13)?"":rows.get(i).get(13).toString());
			pmDto.setRegion(null == rows.get(i).get(14)?"":rows.get(i).get(14).toString());
			pmDto.setLatitude(null == rows.get(i).get(15)?"":rows.get(i).get(15).toString()) ;
			pmDto.setLongitude(null == rows.get(i).get(16)?"":rows.get(i).get(16).toString());
			pmDto.setLaboratory(null == rows.get(i).get(17)?"":rows.get(i).get(17).toString());
			pmDto.setReference(null == rows.get(i).get(18)?"":rows.get(i).get(18).toString());
			pms.add(pmDto);
		}
		return pms;
	}

	/**
	 * 保存PM数据
	 * @return
	 */
	@Transactional
	public synchronized void savePms(List<PmDto> pms) {
		String userId = AppUserTool.getAppUser().getUserID();
		if (null == pms || pms.size() == 0 || StringUtils.isEmpty(userId)) {
			return;
		}
		DataLogDto data = dataLogDao.getLastData("1");
		int rowIndex = 1;
		String rowHash = "";
		String hashKey = PropertiesTool.getString("hashKey");
		if (null != data) {rowIndex = data.getDataIndex() + 1;rowHash = data.getDataHash();}
		String sql  = "INSERT INTO IGDAR_DATALOG_TAB(DATAID,DATATYPE,DATAUPTIME,DATAHASH,DATAUPUSER,DATAINDEX) values";
		for (int i = 0,j = pms.size(); i < j; i++) {
			pms.get(i).setPm_id(UUID.fastUUID().toString());
			String params = hashKey + rowHash + getDataStr(pms.get(i));
			rowHash = Sha256.getSHA256Str(params);
			sql += "('" + pms.get(i).getPm_id() + "','1',NOW(),'" + rowHash + "','" + userId + "','" + (rowIndex + i)  +"'),";
		}
		sql = sql.substring(0,sql.length()-1);
		this.dataLogDao.excute(sql);
		sql = "INSERT INTO IDGAR_PM_TAB(PM_id,EleType,No,IsotopeType,PMType,Content,Ic,icsd,n,mc,mcsd,SamplingDate,Year,Season,SamplingSite,Nation,Region,Latitude,Longitude,Laboratory,Reference) VALUES";
		PmDto pmDto = new PmDto();
		for (int i = 0; i < pms.size(); i++) {
			pmDto = pms.get(i);
			sql+="('" + pmDto.getPm_id()  + "','" + 
					parseString(pmDto.getEleType()) + "','" +
					parseString(pmDto.getNo()) + "','" +
					parseString(pmDto.getIsotopeType()) + "','" + 
					parseString(pmDto.getPMType()) + "','"+ 
					parseString(pmDto.getContent()) +"','"+
					parseString(pmDto.getIc())+"','"+
					parseString(pmDto.getIcsd())+"','"+
					parseString(pmDto.getN())+"','"+
					parseString(pmDto.getMc())+"','"+
					parseString(pmDto.getMcsd())+"','"+
					parseString(pmDto.getSamplingDate())+"','"+
					parseString(pmDto.getYear())+"','"+
					parseString(pmDto.getSeason())+"','"+
					parseString(pmDto.getSamplingSite())+"','"+
					parseString(pmDto.getNation())+"','"+
					parseString(pmDto.getRegion())+"','"+
					parseString(pmDto.getLatitude())+"','"+
					parseString(pmDto.getLongitude())+"','"+
					parseString(pmDto.getLaboratory())+"','"+parseString(pmDto.getReference())+"'),";
		}
		sql = sql.substring(0, sql.length()-1);
		this.dataLogDao.excute(sql);
	}
	
	public static String parseString(String name){
		if(StringUtils.isNotEmpty(name) && name.indexOf("'") > 0 ){
			name = name.replaceAll("'", "''");
		}
		return name;
	}

	public boolean saveData(JSONArray datas) {
		List<PmDto> pms = new ArrayList<PmDto>();
		for (int i = 0; i < datas.size(); i++) {
			pms.add(JSONUtil.toBean(datas.getJSONObject(i), PmDto.class));
		}
		savePms(pms);
		return true;
	}

	/**
	 * 校验数据库PM数据是否发生篡改
	 * @return 0数据正常1空数据2数据异常
	 */
	public int checkIsChange(JdbcTemplate jdbc) {
		Map<String, Object> data = null;
		try {
			data = jdbc.queryForMap("SELECT * from IGDAR_DATALOG_TAB where DATATYPE = '1' ORDER BY DATAINDEX DESC LIMIT 1");
		} catch (Exception e) {
		}
		if (null == data) {
			return 1;
		}
		List<Map<String, Object>> pms = jdbc.queryForList("select a.* from idgar_pm_tab a,igdar_datalog_tab b where a.pm_id = b.dataId ORDER BY b.dataIndex");
		String valHash = "";
		PmDto pmDto = null;
		String hashKey = PropertiesTool.getString("hashKey");
		for (int i = 0,j = pms.size(); i < j; i++) {
			pmDto = JSONUtil.toBean(JSONUtil.parse(pms.get(i)), PmDto.class, false);
			valHash = Sha256.getSHA256Str(hashKey + valHash + getDataStr(pmDto));
		}
		int flag = StringUtils.equals(data.get("dataHash").toString(), valHash)?0:2;
		if (flag == 2) {
			jdbc.execute("delete from idgar_pm_tab");
			jdbc.execute("delete from igdar_datalog_tab where dataType = '1'");
		}
		return flag;
	}

	private String getDataStr(PmDto dto) {
		String params = String.format("%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s%s", 
				dto.getEleType() == null ?"":dto.getEleType(), 
				dto.getIsotopeType() == null ?"":dto.getIsotopeType(), 
				dto.getPMType() == null ?"":dto.getPMType(), 
				dto.getContent() == null ?"":dto.getContent(), 
				dto.getIc() == null ?"":dto.getIc(), 
				dto.getIcsd() == null ?"":dto.getIcsd(), 
				dto.getN() == null ?"":dto.getN(), 
				dto.getMc() == null ?"":dto.getMc(), 
				dto.getMcsd() == null ?"":dto.getMcsd(), 
				dto.getSamplingDate() == null ?"":dto.getSamplingDate(), 
				dto.getYear() == null ?"":dto.getYear(), 
				dto.getSeason() == null ?"":dto.getSeason(), 
				dto.getSamplingSite() == null ?"":dto.getSamplingSite(), 
				dto.getNation() == null ?"":dto.getNation(), 
				dto.getRegion() == null ?"":dto.getRegion(), 
				dto.getLatitude() == null ?"":dto.getLatitude(), 
				dto.getLongitude() == null ?"":dto.getLongitude(), 
				dto.getLaboratory() == null ?"":dto.getLaboratory(), 
				dto.getReference() == null ?"":dto.getReference());

		//		@javax.persistence.Column(name="IsotopeType")
		//		@javax.persistence.Column(name="PMType")
		//		@javax.persistence.Column(name="Content")
		//		@javax.persistence.Column(name="Ic")
		//		@javax.persistence.Column(name="icsd")
		//		@javax.persistence.Column(name="n")
		//		@javax.persistence.Column(name="mc")
		//		@javax.persistence.Column(name="mcsd")
		//		@javax.persistence.Column(name="SamplingDate")
		//		@javax.persistence.Column(name="Year")
		//		@javax.persistence.Column(name="Season")
		//		@javax.persistence.Column(name="SamplingSite")
		//		@javax.persistence.Column(name="Nation")
		//		@javax.persistence.Column(name="Region")
		//		@javax.persistence.Column(name="Latitude")
		//		@javax.persistence.Column(name="Longitude")
		//		@javax.persistence.Column(name="Laboratory")
		//		@javax.persistence.Column(name="Reference")
		return params;
	}
}
